package action.excel;

import com.alibaba.fastjson.JSONObject;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Iterator;
import java.util.List;
import java.util.logging.Logger;

/**
 * @program: study-java8
 * @description:
 * @author: Sun Feng
 * @create: 2020-06-14 15:24
 **/
public class Demo {
    private static Logger logger = Logger.getLogger(Demo.class.getName());
    public static void main(String[] args) {
        // 生成xlsx的Excel
        Workbook workbook = new SXSSFWorkbook();

        // 如需生成xls的Excel，请使用下面的工作簿对象，注意后续输出时文件后缀名也需更改为xls
        //Workbook workbook = new HSSFWorkbook();
        Sheet sheet = workbook.createSheet();
        // 处理表头
        buildCellRangeAddress(sheet);

        // 设置列头宽度
        for (int i=0; i<13; i++) {
            sheet.setColumnWidth(i, 4000);
        }
        sheet.setColumnWidth(13, 6000);
        sheet.setColumnWidth(14, 3000);
        sheet.setColumnWidth(15, 6000);


        // 构建头单元格样式
        CellStyle headCellStyle = buildHeadCellStyle(sheet.getWorkbook());
        // 写入第二行各列的数据
        CellStyle cellStyle1 = buildHeadCellStyleLeft(sheet.getWorkbook());

        // ==================================================
        CellStyle cellStyleCenter = buildHeadCellStyleCenter(sheet.getWorkbook());

        Row head = sheet.createRow(0);
        Cell cell = head.createCell(0);
        cell.setCellValue("交接班报表(泵码)");
        cell.setCellStyle(headCellStyle);



        Row row1 = sheet.createRow(2);
        Cell cell11 = row1.createCell(0);
        cell11.setCellValue("无锡市后桥东风加油站 日期 2020-06-14 班次:1");
        cell11.setCellStyle(cellStyle1);
        Row row2 = sheet.createRow(4);
        Cell cell21 = row2.createCell(0);
        cell21.setCellValue("无锡市后桥东风加油站 日期 2020-06-14 班次:1");
        cell21.setCellStyle(cellStyle1);


        buildCellHeader(sheet);


        String json="[{\"id\":\"314\",\"gunId\":\"1\",\"shiftTimeNotesId\":\"177\",\"startPumpCode\":739092.63,\"endPumpCode\":741058.02,\"price\":5.19,\"amount\":10200.3741,\"totalPumpCode\":1965.39,\"totalQuantity\":1965.39,\"canReturnPrice\":0,\"canReturnQuantity\":0,\"canReturnAmount\":0,\"selfUseOilPrice\":0,\"selfUseOilQuantity\":0,\"selfUseOilAmount\":0,\"writeOfAccidentsPrice\":0,\"writeOfAccidentsQuantity\":0,\"writeOfAccidentsAmount\":0,\"version\":\"1\",\"creator\":null,\"createDate\":null,\"lastModifiedDate\":null},{\"id\":\"315\",\"gunId\":\"2\",\"shiftTimeNotesId\":\"177\",\"startPumpCode\":141357.17,\"endPumpCode\":141775.04,\"price\":5.54,\"amount\":2314.9998,\"totalPumpCode\":417.87,\"totalQuantity\":417.87,\"canReturnPrice\":0,\"canReturnQuantity\":0,\"canReturnAmount\":0,\"selfUseOilPrice\":0,\"selfUseOilQuantity\":0,\"selfUseOilAmount\":0,\"writeOfAccidentsPrice\":0,\"writeOfAccidentsQuantity\":0,\"writeOfAccidentsAmount\":0,\"version\":\"1\",\"creator\":null,\"createDate\":null,\"lastModifiedDate\":null},{\"id\":\"316\",\"gunId\":\"3\",\"shiftTimeNotesId\":\"177\",\"startPumpCode\":209951.11,\"endPumpCode\":210445.19,\"price\":5.07,\"amount\":2504.9856,\"totalPumpCode\":494.08,\"totalQuantity\":494.08,\"canReturnPrice\":0,\"canReturnQuantity\":0,\"canReturnAmount\":0,\"selfUseOilPrice\":0,\"selfUseOilQuantity\":0,\"selfUseOilAmount\":0,\"writeOfAccidentsPrice\":0,\"writeOfAccidentsQuantity\":0,\"writeOfAccidentsAmount\":0,\"version\":\"1\",\"creator\":null,\"createDate\":null,\"lastModifiedDate\":null},{\"id\":\"317\",\"gunId\":\"4\",\"shiftTimeNotesId\":\"177\",\"startPumpCode\":168545.58,\"endPumpCode\":168911.66,\"price\":5.07,\"amount\":1856.0256,\"totalPumpCode\":366.08,\"totalQuantity\":366.08,\"canReturnPrice\":0,\"canReturnQuantity\":0,\"canReturnAmount\":0,\"selfUseOilPrice\":0,\"selfUseOilQuantity\":0,\"selfUseOilAmount\":0,\"writeOfAccidentsPrice\":0,\"writeOfAccidentsQuantity\":0,\"writeOfAccidentsAmount\":0,\"version\":\"1\",\"creator\":null,\"createDate\":null,\"lastModifiedDate\":null},{\"id\":\"318\",\"gunId\":\"5\",\"shiftTimeNotesId\":\"177\",\"startPumpCode\":550573.35,\"endPumpCode\":551910.18,\"price\":5.19,\"amount\":6938.1477,\"totalPumpCode\":1336.83,\"totalQuantity\":1336.83,\"canReturnPrice\":0,\"canReturnQuantity\":0,\"canReturnAmount\":0,\"selfUseOilPrice\":0,\"selfUseOilQuantity\":0,\"selfUseOilAmount\":0,\"writeOfAccidentsPrice\":0,\"writeOfAccidentsQuantity\":0,\"writeOfAccidentsAmount\":0,\"version\":\"1\",\"creator\":null,\"createDate\":null,\"lastModifiedDate\":null},{\"id\":\"319\",\"gunId\":\"6\",\"shiftTimeNotesId\":\"177\",\"startPumpCode\":457350.56,\"endPumpCode\":458660.16,\"price\":5.19,\"amount\":6796.8240,\"totalPumpCode\":1309.60,\"totalQuantity\":1309.60,\"canReturnPrice\":0,\"canReturnQuantity\":0,\"canReturnAmount\":0,\"selfUseOilPrice\":0,\"selfUseOilQuantity\":0,\"selfUseOilAmount\":0,\"writeOfAccidentsPrice\":0,\"writeOfAccidentsQuantity\":0,\"writeOfAccidentsAmount\":0,\"version\":\"1\",\"creator\":null,\"createDate\":null,\"lastModifiedDate\":null},{\"id\":\"320\",\"gunId\":\"7\",\"shiftTimeNotesId\":\"177\",\"startPumpCode\":176877.99,\"endPumpCode\":177490.44,\"price\":5.54,\"amount\":3392.9730,\"totalPumpCode\":612.45,\"totalQuantity\":612.45,\"canReturnPrice\":0,\"canReturnQuantity\":0,\"canReturnAmount\":0,\"selfUseOilPrice\":0,\"selfUseOilQuantity\":0,\"selfUseOilAmount\":0,\"writeOfAccidentsPrice\":0,\"writeOfAccidentsQuantity\":0,\"writeOfAccidentsAmount\":0,\"version\":\"1\",\"creator\":null,\"createDate\":null,\"lastModifiedDate\":null},{\"id\":\"321\",\"gunId\":\"8\",\"shiftTimeNotesId\":\"177\",\"startPumpCode\":321216.12,\"endPumpCode\":321776.06,\"price\":5.19,\"amount\":2906.0886,\"totalPumpCode\":559.94,\"totalQuantity\":559.94,\"canReturnPrice\":0,\"canReturnQuantity\":0,\"canReturnAmount\":0,\"selfUseOilPrice\":0,\"selfUseOilQuantity\":0,\"selfUseOilAmount\":0,\"writeOfAccidentsPrice\":0,\"writeOfAccidentsQuantity\":0,\"writeOfAccidentsAmount\":0,\"version\":\"1\",\"creator\":null,\"createDate\":null,\"lastModifiedDate\":null}]";

        List<ReportPumpCodeDto> dtos = JSONObject.parseArray(json, ReportPumpCodeDto.class);


        int rowNum = 8;
        for (Iterator<ReportPumpCodeDto> it = dtos.iterator(); it.hasNext(); ) {
            ReportPumpCodeDto data = it.next();
            if (data == null) {
                continue;
            }
            //输出行数据
            Row row = sheet.createRow(rowNum++);
            convertDataToRow(data, row);
        }

        // 合并下方单元格




/**
 *  合并单元格
 */
        CellRangeAddress address22 = new CellRangeAddress(18, 19, 0, 0);
        sheet.addMergedRegion(address22);

        CellRangeAddress address221 = new CellRangeAddress(18, 19, 1, 1);
        sheet.addMergedRegion(address221);

        CellRangeAddress address24 = new CellRangeAddress(20, 23, 0, 0);
        sheet.addMergedRegion(address24);

        CellRangeAddress address241 = new CellRangeAddress(20, 23, 1, 1);
        sheet.addMergedRegion(address241);

        /*线下支付 pos 和 现金的两个单元格*/
        CellRangeAddress address222 = new CellRangeAddress(20, 21, 2, 2);
        sheet.addMergedRegion(address222);

        CellRangeAddress address242 = new CellRangeAddress(22, 23, 2, 2);
        sheet.addMergedRegion(address242);

        CellRangeAddress address223 = new CellRangeAddress(20, 21, 3, 3);
        sheet.addMergedRegion(address223);

        CellRangeAddress address243 = new CellRangeAddress(22, 23, 3, 3);
        sheet.addMergedRegion(address243);



        CellRangeAddress address28 = new CellRangeAddress(24, 25, 0, 0);
        sheet.addMergedRegion(address28);
        CellRangeAddress address281 = new CellRangeAddress(24, 25, 1, 1);
        sheet.addMergedRegion(address281);


        CellRangeAddress address30 = new CellRangeAddress(26, 27, 0, 0);
        sheet.addMergedRegion(address30);

        CellRangeAddress address301 = new CellRangeAddress(26, 27, 1, 1);
        sheet.addMergedRegion(address301);

        CellRangeAddress address32 = new CellRangeAddress(28, 29, 0, 0);
        sheet.addMergedRegion(address32);

        CellRangeAddress address321 = new CellRangeAddress(28, 29, 1, 1);
        sheet.addMergedRegion(address321);


//        CellRangeAddress address34 = new CellRangeAddress(30, 31, 0, 0);
//        sheet.addMergedRegion(address34);
//
//        CellRangeAddress address341 = new CellRangeAddress(30, 31, 1, 1);
//        sheet.addMergedRegion(address341);


        Row row22 = sheet.createRow(18);
        Cell cell221 = row22.createCell(0);
        cell221.setCellValue("客户加油卡");
        cell221.setCellStyle(cellStyleCenter);


        Cell cell222 = row22.createCell(1);
        cell222.setCellValue("客户加油卡Value");
        cell222.setCellStyle(cellStyleCenter);


        Row row24 = sheet.createRow(20);
        Cell cell241 = row24.createCell(0);
        cell241.setCellValue("线下支付");
        cell241.setCellStyle(cellStyleCenter);

        Cell cell242 = row24.createCell(1);
        cell242.setCellValue("线下支付value");
        cell242.setCellStyle(cellStyleCenter);

        // 这里分两种情况处理

        Cell cell2032= row24.createCell(2);
        cell2032.setCellValue("线下支付11");
        cell2032.setCellStyle(cellStyleCenter);

        Cell cell2033= row24.createCell(3);
        cell2033.setCellValue("线下支付value");
        cell2033.setCellStyle(cellStyleCenter);


        Row row23 = sheet.createRow(22);

        Cell cell232= row23.createCell(2);
        cell232.setCellValue("线下支付key");
        cell232.setCellStyle(cellStyleCenter);

        Cell cell233= row23.createCell(3);
        cell233.setCellValue("线下支付key");
        cell233.setCellStyle(cellStyleCenter);





        Row row28 = sheet.createRow(24);
        Cell cell281= row28.createCell(0);
        cell281.setCellValue("线上支付");
        cell281.setCellStyle(cellStyleCenter);

        Cell cell282= row28.createCell(1);
        cell282.setCellValue("线上支付value");
        cell282.setCellStyle(cellStyleCenter);



        Row row30 = sheet.createRow(26);
        Cell cell301= row30.createCell(0);
        cell301.setCellValue("优惠金额");
        cell301.setCellStyle(cellStyleCenter);

        Cell cell302= row30.createCell(1);
        cell302.setCellValue("优惠金额value");
        cell302.setCellStyle(cellStyleCenter);


        Row row32 = sheet.createRow(28);
        Cell cell321= row32.createCell(0);
        cell321.setCellValue("差异");
        cell321.setCellStyle(cellStyleCenter);

        Cell cell322= row32.createCell(1);
        cell322.setCellValue("差异value");
        cell322.setCellStyle(cellStyleCenter);

        FileOutputStream fileOut = null;
        try {
            String exportFilePath = "F:\\example1.xlsx";
            File exportFile = new File(exportFilePath);
            System.out.println(exportFile);
            if (!exportFile.exists()) {
                exportFile.createNewFile();
            }

            fileOut = new FileOutputStream(exportFilePath);
            workbook.write(fileOut);
            fileOut.flush();
        } catch (Exception e) {
            logger.warning("输出Excel时发生错误，错误原因：" + e.getMessage());
        } finally {
            try {
                if (null != fileOut) {
                    fileOut.close();
                }
                if (null != workbook) {
                    workbook.close();
                }
            } catch (IOException e) {
                logger.warning("关闭输出流时发生错误，错误原因：" + e.getMessage());
            }
        }
    }

    private static void buildCellHeader(Sheet sheet) {
        // ==================================================
        CellStyle cellStyleCenter = buildHeadCellStyleCenter(sheet.getWorkbook());
        // 写入第4行各列的数据
        Row row3 = sheet.createRow(6);
        Cell cell31 = row3.createCell(0);
        cell31.setCellValue("枪号");
        cell31.setCellStyle(cellStyleCenter);

        Cell cell32 = row3.createCell(1);
        cell32.setCellValue("泵码接班数");
        cell32.setCellStyle(cellStyleCenter);

        Cell cell33 = row3.createCell(2);
        cell33.setCellValue("泵码交班数");
        cell33.setCellStyle(cellStyleCenter);

        Cell cell34 = row3.createCell(3);
        cell34.setCellValue("泵码合计");
        cell34.setCellStyle(cellStyleCenter);

        Cell cell35 = row3.createCell(4);
        cell35.setCellValue("回灌");
        cell35.setCellStyle(cellStyleCenter);

        Cell cell36 = row3.createCell(7);
        cell36.setCellValue("自用油");
        cell36.setCellStyle(cellStyleCenter);

        Cell cell37 = row3.createCell(10);
        cell37.setCellValue("事故核销");
        cell37.setCellStyle(cellStyleCenter);

        Cell cell38 = row3.createCell(13);
        cell38.setCellValue("机油销售数量合计");
        cell38.setCellStyle(cellStyleCenter);
        Cell cell39 = row3.createCell(14);
        cell39.setCellValue("单价");
        cell39.setCellStyle(cellStyleCenter);
        Cell cell310 = row3.createCell(15);
        cell310.setCellValue("机油销售金额");
        cell310.setCellStyle(cellStyleCenter);


        Row row4 = sheet.createRow(7);
        Cell cell45 = row4.createCell(4);
        cell45.setCellValue("单价");
        cell45.setCellStyle(cellStyleCenter);

        Cell cell46 = row4.createCell(5);
        cell46.setCellValue("数量");
        cell46.setCellStyle(cellStyleCenter);

        Cell cell47 = row4.createCell(6);
        cell47.setCellValue("金额");
        cell47.setCellStyle(cellStyleCenter);


        Cell cell55 = row4.createCell(7);
        cell55.setCellValue("单价");
        cell55.setCellStyle(cellStyleCenter);

        Cell cell56 = row4.createCell(8);
        cell56.setCellValue("数量");
        cell56.setCellStyle(cellStyleCenter);

        Cell cell57 = row4.createCell(9);
        cell57.setCellValue("金额");
        cell57.setCellStyle(cellStyleCenter);

        Cell cell58 = row4.createCell(10);
        cell58.setCellValue("金额");
        cell58.setCellStyle(cellStyleCenter);
        Cell cell59 = row4.createCell(11);
        cell59.setCellValue("金额");
        cell59.setCellStyle(cellStyleCenter);
        Cell cell510 = row4.createCell(12);
        cell510.setCellValue("金额");
        cell510.setCellStyle(cellStyleCenter);
    }

    /**
     *  合并单元格
     * @param sheet
     */
    private static void buildCellRangeAddress(Sheet sheet) {
        // 处理表头
        CellRangeAddress cellrange = new CellRangeAddress(0, 1, 0, 15);
        CellRangeAddress cellrange2 = new CellRangeAddress(2, 3, 0, 15);
        CellRangeAddress cellrange3 = new CellRangeAddress(4, 5, 0, 15);
        CellRangeAddress cellrange8 = new CellRangeAddress(6, 6, 4, 6);
        CellRangeAddress cellrange9 = new CellRangeAddress(6, 6, 7, 9);
        CellRangeAddress cellrange10 = new CellRangeAddress(6, 6, 10, 12);
        CellRangeAddress cellrange4 = new CellRangeAddress(6, 7, 0, 0);
        CellRangeAddress cellrange5 = new CellRangeAddress(6, 7, 1, 1);
        CellRangeAddress cellrange6 = new CellRangeAddress(6, 7, 2, 2);
        CellRangeAddress cellrange7 = new CellRangeAddress(6, 7, 3, 3);
        CellRangeAddress cellrange13 = new CellRangeAddress(6, 7, 13, 13);
        CellRangeAddress cellrange14 = new CellRangeAddress(6, 7, 14, 14);
        CellRangeAddress cellrange15 = new CellRangeAddress(6, 7, 15, 15);
        // 设置默认行高
        sheet.addMergedRegion(cellrange);
        sheet.addMergedRegion(cellrange2);
        sheet.addMergedRegion(cellrange3);
        sheet.addMergedRegion(cellrange4);
        sheet.addMergedRegion(cellrange5);
        sheet.addMergedRegion(cellrange6);
        sheet.addMergedRegion(cellrange7);
        sheet.addMergedRegion(cellrange8);
        sheet.addMergedRegion(cellrange9);
        sheet.addMergedRegion(cellrange10);
        sheet.addMergedRegion(cellrange13);
        sheet.addMergedRegion(cellrange14);
        sheet.addMergedRegion(cellrange15);
    }


    /**
     * 将数据转换成行
     * @param data 源数据
     * @param row 行对象
     * @return
     */
    private static void convertDataToRow(ReportPumpCodeDto data, Row row){
        int cellNum = 0;
        Cell cell;

        // 枪号
        cell = row.createCell(cellNum++);
        cell.setCellValue(null == data.getGunId() ? "" : data.getGunId().toString());
        // 泵码接班数
        cell = row.createCell(cellNum++);
        cell.setCellValue(null == data.getStartPumpCode() ? "" : data.getStartPumpCode().toString());

        // 泵码交班数
        cell = row.createCell(cellNum++);
        cell.setCellValue(null == data.getEndPumpCode() ? "" : data.getEndPumpCode().toString());
        // 泵码合计
        cell = row.createCell(cellNum++);
        cell.setCellValue(null == data.getTotalPumpCode() ? "" : data.getTotalPumpCode().toString());

        // 回灌单价
        cell = row.createCell(cellNum++);
        cell.setCellValue(null == data.getCanReturnPrice() ? "" : data.getCanReturnPrice().toString());
        // 回灌数量
        cell = row.createCell(cellNum++);
        cell.setCellValue(null == data.getCanReturnQuantity() ? "" : data.getCanReturnQuantity().toString());
        // 回灌金额
        cell = row.createCell(cellNum++);
        cell.setCellValue(null == data.getCanReturnAmount() ? "" : data.getCanReturnAmount().toString());

        // 自用油单价
        cell = row.createCell(cellNum++);
        cell.setCellValue(null == data.getSelfUseOilPrice() ? "" : data.getSelfUseOilPrice().toString());
        // 自用油数量
        cell = row.createCell(cellNum++);
        cell.setCellValue(null == data.getSelfUseOilQuantity() ? "" : data.getSelfUseOilQuantity().toString());
        // 自用油金额
        cell = row.createCell(cellNum++);
        cell.setCellValue(null == data.getSelfUseOilAmount() ? "" : data.getSelfUseOilAmount().toString());

        // 自用油单价
        cell = row.createCell(cellNum++);
        cell.setCellValue(null == data.getWriteOfAccidentsPrice() ? "" : data.getWriteOfAccidentsPrice().toString());
        // 自用油数量
        cell = row.createCell(cellNum++);
        cell.setCellValue(null == data.getWriteOfAccidentsQuantity() ? "" : data.getWriteOfAccidentsQuantity().toString());
        // 自用油金额
        cell = row.createCell(cellNum++);
        cell.setCellValue(null == data.getWriteOfAccidentsAmount() ? "" : data.getWriteOfAccidentsAmount().toString());

        // 油机销售数量合计
        cell = row.createCell(cellNum++);
        cell.setCellValue(null == data.getTotalQuantity() ? "" : data.getTotalQuantity().toString());
        // 单价
        cell = row.createCell(cellNum++);
        cell.setCellValue(null == data.getPrice() ? "" : data.getPrice().toString());
        // 油机销售金额
        cell = row.createCell(cellNum++);
        cell.setCellValue(null == data.getAmount() ? "" : data.getAmount().toString());


    }
    /**
     * 设置第一行列头的样式
     * @param workbook 工作簿对象
     * @return 单元格样式对象
     */
    private static CellStyle buildHeadCellStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        //对齐方式设置  左右
        style.setAlignment(HorizontalAlignment.CENTER);
        // 上下
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        //边框颜色和宽度设置
        style.setBorderBottom(BorderStyle.THICK);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); // 下边框
        style.setBorderLeft(BorderStyle.THICK);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); // 左边框
        style.setBorderRight(BorderStyle.THICK);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex()); // 右边框
        style.setBorderTop(BorderStyle.THICK);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex()); // 上边框
        //设置背景颜色
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        //粗体字设置
        Font font = workbook.createFont();
        font.setBold(true);
        style.setFont(font);
        return style;
    }
    /**
     * 设置左边样式
     * @param workbook 工作簿对象
     * @return 单元格样式对象
     */
    private static CellStyle buildHeadCellStyleLeft(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        //对齐方式设置 左右
        style.setAlignment(HorizontalAlignment.LEFT);
        // s上下
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        //粗体字设置
        Font font = workbook.createFont();
        font.setBold(true);
        style.setFont(font);
        return style;
    }

    /**
     *  设置center
     * @param workbook
     * @return
     */
    private static CellStyle buildHeadCellStyleCenter(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        //对齐方式设置
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        //粗体字设置
        Font font = workbook.createFont();
       font.setFontName("黑体");
        style.setFont(font);
        return style;
    }
}
